package com.keke.ado.user;

import com.keke.ado.baseDao;
import com.keke.pojo.Role;
import com.keke.pojo.User;
import com.mysql.cj.Constants;
import com.mysql.cj.util.StringUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDaoImpl implements UserDao {
    //得到登录的用户
    public User getLoginUser(Connection connection, String userCode,String userPassword) throws SQLException {

        PreparedStatement preparedStatement = null;
        ResultSet resultSet=null;
        User user=null;

        if(connection!=null){
            String sql="select * from smbms_user where userCode=?";
            Object[] params = {userCode};

            resultSet = baseDao.execute(connection, preparedStatement, resultSet, sql, params);

            if(resultSet.next()){
                user= new User();
                user.setId(resultSet.getInt("id"));
                user.setUserCode(resultSet.getString("userCode"));
                user.setUserName(resultSet.getString("userName"));
                user.setUserPassword(resultSet.getString("userPassword"));
                user.setGender(resultSet.getInt("gender"));
                user.setBirthday(resultSet.getDate("birthday"));
                user.setPhone(resultSet.getString("phone"));
                user.setAddress(resultSet.getString("address"));
                user.setUserRole(resultSet.getInt("userRole"));
                user.setCreatedBy(resultSet.getInt("createdBy"));
                user.setCreationDate(resultSet.getTimestamp("creationDate"));
                user.setModifyBy(resultSet.getInt("modifyBy"));
                user.setModifyDate(resultSet.getTimestamp("modifyDate"));
            }
            //关闭
            baseDao.close(null,preparedStatement,resultSet);
            if(!user.getUserPassword().equals(userPassword)){
                user=null;
            }
        }

        return user;
    }

    //修改当前用户密码
    public int updatePwd(Connection connection, int id, String password) throws SQLException {
        int execute=0;
        PreparedStatement preparedStatement=null;

        if(connection!=null){
            String sql="update smbms_user set userPassword = ? where id = ?";
            Object params[]={password,id};
            execute=baseDao.execute(connection,preparedStatement,sql,params);
            baseDao.close(null,preparedStatement,null);
        }

        return execute;
    }

    //根据用户名或者角色查询用户总数
    public int getUserCount(Connection connection, String username, int userRole) throws SQLException {
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;
        int count=0;

        if(connection!=null){
            StringBuffer sql= new StringBuffer();
            sql.append("select count(1) as count from smbms_user u,smbms_role r where u.userRole = r.id");
            ArrayList<Object> list = new ArrayList<Object>();//存放参数

            if(!StringUtils.isNullOrEmpty(username)){
                sql.append(" and u.userName like ?");
                list.add("%"+username+"%");//index:0
            }

            if(userRole>0){
                sql.append(" and u.userRole = ?");
                list.add(userRole);//index:1
            }

            //把list转为数组
            Object[] params = list.toArray();
            System.out.println("UserDaoImpl->getCount():"+sql.toString());

            resultSet = baseDao.execute(connection, preparedStatement, resultSet, sql.toString(), params);

            if(resultSet.next()){
                count=resultSet.getInt("count");  //从结果集中获取最终的数量
            }
            baseDao.close(null,preparedStatement,resultSet);
        }
        return  count;
    }

    //获取用户列表
    public List<User> getUserList(Connection connection, String username, int userRole, int currentPageNo, int pageSize) throws SQLException {
        PreparedStatement preparedStatement =null;
        ResultSet resultSet =null;
        List<User> userList=new ArrayList<User>();
        if(connection!=null){
            StringBuffer sql= new StringBuffer();
            sql.append("select u.*,r.roleName as userRoleName from smbms_user u,smbms_role r where u.userRole = r.id");
            List<Object> list=new ArrayList<Object>();
            if(!StringUtils.isNullOrEmpty(username)){
                sql.append(" and u.userName like ?");
                list.add("%"+username+"%");
            }
            if(userRole>0){
                sql.append(" and u.userRole = ?");
                list.add(userRole);
            }
            sql.append(" order by creationDate DESC limit ?,?");
            currentPageNo=(currentPageNo-1)*pageSize;
            list.add(currentPageNo);
            list.add(pageSize);

            Object[] params = list.toArray();
            for (Object param : params) {
                System.out.println(param);
            }
            System.out.println("getUserList--->sql: "+sql.toString());
            resultSet=baseDao.execute(connection,preparedStatement,resultSet,sql.toString(),params);
            while (resultSet.next()){
                User _user = new User();
                _user.setId(resultSet.getInt("id"));
                _user.setUserCode(resultSet.getString("userCode"));
                _user.setUserName(resultSet.getString("userName"));
                _user.setGender(resultSet.getInt("gender"));
                _user.setBirthday(resultSet.getDate("birthday"));
                _user.setPhone(resultSet.getString("phone"));
                _user.setUserRole(resultSet.getInt("userRole"));
                _user.setUserRoleName(resultSet.getString("userRoleName"));
                userList.add(_user);
            }
            baseDao.close(null,preparedStatement,resultSet);
        }
        return userList;
    }

    //添加用户
    public int add(Connection connection, User user) throws SQLException {
        PreparedStatement preparedStatement=null;
        int updateRows=0;
        if(connection!=null){
            String sql="insert into smbms_user(userCode,userName,userPassword," +
                    "userRole,gender,birthday,phone,address,creationDate,createdBy)" +
                    "values(?,?,?,?,?,?,?,?,?,?)";
            Object[] param={user.getUserCode(),user.getUserName(),user.getUserPassword(),
                            user.getUserRole(),user.getGender(),user.getBirthday(),
                            user.getPhone(),user.getAddress(),user.getCreationDate(),user.getCreatedBy()};
            updateRows=baseDao.execute(connection,preparedStatement,sql,param);
            baseDao.close(null,preparedStatement,null);

        }
        return updateRows;
    }

    //删除用户
    public int deleteUserById(Connection connection, int delId) throws SQLException {
        PreparedStatement preparedStatement=null;
        int updateRows=0;
        if(connection!=null){
            String sql = "delete from smbms_user where id = ?";
            Object[] param={delId};
            updateRows=baseDao.execute(connection,preparedStatement,sql,param);
            baseDao.close(null,preparedStatement,null);
        }
        return updateRows;
    }

    //利用id查询user
    public User getUserById(Connection connection, String id) throws SQLException {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet =null;
        User user =new User();
        if(connection!=null){
            String sql = "select u.*,r.roleName as userRoleName from smbms_user u,smbms_role r where u.userRole = r.id and u.id = ?";
            Object [] params= {id};
            resultSet=baseDao.execute(connection,preparedStatement,resultSet,sql,params);
            while (resultSet.next()){
                user.setId(resultSet.getInt("id"));
                user.setUserCode(resultSet.getString("userCode"));
                user.setUserName(resultSet.getString("userName"));
                user.setGender(resultSet.getInt("gender"));
                user.setBirthday(resultSet.getDate("birthday"));
                user.setPhone(resultSet.getString("phone"));
                user.setAddress(resultSet.getString("address"));
                user.setUserRole(resultSet.getInt("userRole"));
                user.setUserRoleName(resultSet.getString("userRoleName"));
                break;
            }
            baseDao.close(null,preparedStatement,null);
        }
        return user;
    }

    //修改用户
    public int modify(Connection connection, User user) throws SQLException {
        PreparedStatement pstm = null;
        int updateRows = 0;
        if (connection != null) {
            String sql = "update smbms_user set userCode=?, userName=?, gender=?, birthday=?, phone=?, " +
                    "address=?, userRole=?, modifyBy=?, modifyDate=? where id=?";
            Object[] params = {user.getUserCode(), user.getUserName(), user.getGender(), user.getBirthday(),
                    user.getPhone(), user.getAddress(), user.getUserRole(), user.getModifyBy(),
                    user.getModifyDate(), user.getId()};
            updateRows = baseDao.execute(connection, pstm, sql, params);
            baseDao.close(null, pstm, null);
        }
        return updateRows;
    }

    //获取用户编码是否存在
    public User getUserByUserCode(Connection connection, String userCode) throws SQLException {
        PreparedStatement pstm = null;
        ResultSet rs = null;
        User user = new User();
        if (connection != null) {
            String sql = "select * from smbms_user where userCode = ?";
            ArrayList<String> tmp = new ArrayList<String>();
            tmp.add(userCode);
            Object[] params = tmp.toArray();
            rs = baseDao.execute(connection, pstm, rs,sql, params);
            while (rs.next()) {
                user.setId(rs.getInt("id"));
                user.setUserCode(rs.getString("userCode"));
                user.setUserName(rs.getString("userName"));
                user.setGender(rs.getInt("gender"));
                user.setBirthday(rs.getDate("birthday"));
                user.setPhone(rs.getString("phone"));
                user.setUserRole(rs.getInt("userRole"));
                break;
            }
            baseDao.close(null, pstm, rs);
        }
        return user;
    }
}
